package way;

import common.Utils.UUIDGenerator;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;

public class upload {

    public static Connection con=null;

    public static void build(Connection conn) throws BiffException, IOException{
        String filepath="D://eloan.xlsx";
        Workbook book = null;
        Row row = null;
        CallableStatement st = null;
        String id= null;
        String columns[] = {"DATA_TYPE_NO","DATA_TYPE_NAME","DATA_NO","DATA_NAME"};


        try {
            book = readExcel(filepath);
            Sheet sheet = book.getSheetAt(0);//工作簿是从0开始的
            //获取第一个sheet
            sheet = book.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();

            for (int i = 1; i < rownum; i++) {
                Map<String, String> map = new LinkedHashMap<String, String>();
                row = sheet.getRow(i);
                if (row != null) {
                    for (int j = 0; j < colnum; j++) {
                        String cellData = (String) getCellFormatValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                    id = UUIDGenerator.generate();
                    st = conn.prepareCall("insert into DIC(ID,DATA_TYPE_NO,DATA_TYPE_NAME,DATA_NO,DATA_NAME) VALUES(?,?,?,?,?)");//这里是执行插入操作，可以换成别的如UPDATE

                    st.setString(1, id);
                    st.setString(2, map.get("DATA_TYPE_NO"));
                    st.setString(3, map.get("DATA_TYPE_NAME"));
                    st.setString(4, map.get("DATA_NO"));
                    st.setString(5, map.get("DATA_NAME"));
                    st.execute();
                    st.close();
                    System.out.println("第" + i + "条记录！");
                } else {
                    break;
                }
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }

    //读取excel
    private static Workbook readExcel(String excelfile) {
        Workbook wb = null;
        if(excelfile==null){
            return null;
        }
        String extString = excelfile.substring(excelfile.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(excelfile);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    public static  Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{//配置JDBC连接
        //链接Oracle
        String url = "jdbc:oracle:thin:@10.3.20.83:1521:ORCL";
       // Class.forName("oracle.jdbc.driver.OracleDriver");
        Class.forName("oracle.jdbc.OracleDriver").newInstance();
        con = DriverManager.getConnection(url, "qqhs", "123456");

        return con;

    }


    public static void main(String [] arg) throws BiffException, IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{

        Connection co = upload.getConnection();
        co.setAutoCommit(true);
        upload.build(co);
        co.close();
        System.out.println("JUST OK!!!");

    }

}
